package water.com.util;

import water.com.DaoSupport.entity.User;
import org.junit.Test;

import java.lang.reflect.AccessibleObject;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * SQL自动生成器
 * @param <T>
 */
public class WaterSQLGeneratorImp<T> implements WaterSQLGenerator<T>{
    /**
     * 根据传入模型自动生成单表select SQL语句
     * @param model 表模型
     * @return SQL语句
     */
    public String selectGenerator(T model) throws IllegalAccessException {
        if(model==null){
            return null;
        }
        StringBuilder sql= new StringBuilder();
        List<String> allColumns = new ArrayList<>();
        Map<String,String> conditionsMap = new HashMap<>();
        Class<?> cl=model.getClass();
        Field[] fields=cl.getDeclaredFields();
        AccessibleObject.setAccessible(fields,true);
        for(Field field:fields){
            allColumns.add(Built.toColumnName(field.getName()));
            if(field.get(model)!=null){
                conditionsMap.put(Built.toColumnName(field.getName()),String.valueOf(field.get(model)));
            }
        }
        //开始拼接语句
        sql.append("SELECT ");
        for(String column:allColumns){
            sql.append(column).append(",");
        }
        sql.replace(sql.lastIndexOf(","),sql.lastIndexOf(",")+1," ");
        sql.append("FROM ").append(Built.toTableName(cl.getSimpleName())).append(" ");
        if(conditionsMap.size()>0){
            sql.append("WHERE ");
            for (Map.Entry<String, String> entry : conditionsMap.entrySet()) {
                String mapKey = entry.getKey();
                String mapValue = entry.getValue();
                sql.append(mapKey).append("=").append("\"").append(mapValue).append("\"").append(" ").append("AND ");
            }
            sql.replace(sql.lastIndexOf("AND"),sql.lastIndexOf("AND")+3," ");
        }
        return sql.toString();
    }
    @Test
    public void sqlGeneratorTest() throws IllegalAccessException {
        User user =new User();
        user.setPassword("32354235");
        List<String> columns= new ArrayList<>();
        columns.add("id");
        columns.add("code");
        System.out.println(selectGenerator((T) user,columns));
    }

    /**
     * 根据传入模型和字符串列表生产查询指定列的SQL语句
     * @param model 表模型
     * @param columns 列字符串
     * @return
     */
    public String selectGenerator(T model, List<String> columns) throws IllegalAccessException {
        StringBuilder sql= new StringBuilder();
        Map<String,String> conditionsMap = new HashMap<>();
        Class<?> cl=model.getClass();
        Field[] fields=cl.getDeclaredFields();
        AccessibleObject.setAccessible(fields,true);
        for(Field field:fields){
            if(field.get(model)!=null){
                conditionsMap.put(Built.toColumnName(field.getName()),String.valueOf(field.get(model)));
            }
        }
        //开始拼接语句
        sql.append("SELECT ");
        for(String column: columns){
            sql.append(Built.toColumnName(column)).append(",");
        }
        sql.replace(sql.lastIndexOf(","),sql.lastIndexOf(",")+1," ");
        sql.append("FROM ").append(Built.toTableName(cl.getSimpleName())).append(" ").append("WHERE ");
        for (Map.Entry<String, String> entry : conditionsMap.entrySet()) {
            String mapKey = entry.getKey();
            String mapValue = entry.getValue();
            sql.append(mapKey).append("=").append("\"").append(mapValue).append("\"").append(" ").append("AND ");
        }
        sql.replace(sql.lastIndexOf("AND"),sql.lastIndexOf("AND")+3," ");
        return sql.toString();
    }

    /**
     * 自动生成update语句: update table_name set ..... where ....
     * 其中model中有值的字段将作为更新内容，conditionsMap中的键值对作为更新条件
     * 注意：此sql语句生成方法存在一种情况：无法满足将指定字段置为null的场景
     * @param model 表模型
     * @param conditionsMap 更新条件
     * @return 返回SQL语句
     */
    public String updateGenerator(T model, Map<String,String> conditionsMap) throws IllegalAccessException {
        Map<String,String> newFieldMap = new HashMap<>();
        StringBuilder sql= new StringBuilder();
        Class<?> cl = model.getClass();
        Field[] fields = cl.getDeclaredFields();
        AccessibleObject.setAccessible(fields,true);
        for(Field field:fields){
            if(field.get(model)!=null){
                newFieldMap.put(Built.toColumnName(field.getName()),String.valueOf(field.get(model)));
            }
        }
        //开始拼接SQL语句
        sql.append("UPDATE ").append(Built.toTableName(cl.getSimpleName())).append(" SET ");
        for (Map.Entry<String, String> entry : newFieldMap.entrySet()) {
            String mapKey = entry.getKey();
            String mapValue = entry.getValue();
            sql.append(mapKey).append("=").append("\"").append(mapValue).append("\"").append(",");
        }
        sql.replace(sql.lastIndexOf(","),sql.lastIndexOf(",")+1," ");
        sql.append("WHERE ");
        for (Map.Entry<String, String> entry : conditionsMap.entrySet()) {
            String mapKey = entry.getKey();
            String mapValue = entry.getValue();
            sql.append(Built.toColumnName(mapKey)).append("=").append("\"").append(mapValue).append("\"").append(" ").append("AND ");
        }
        sql.replace(sql.lastIndexOf("AND"),sql.lastIndexOf("AND")+3," ");

        return sql.toString();
    }
    @Test
    public void updateSqlTest() throws IllegalAccessException {
        User user =new User();
        user.setPassword("32354235");
        user.setUname("严林静");
        user.setId("888");
        System.out.println(updateGenerator((T) user));
    }

    /**
     * 自动生成主键条件update语句: update table_name set ..... where internalKey = ...
     * 其中model中有值的字段将作为更新内容，配置主键作为更新条件
     * 注意：此sql语句生成方法存在一种情况：无法满足将指定字段置为null的场景
     * @param model 表模型
     * @return 返回SQL语句
     */
    public String updateGenerator(T model) throws IllegalAccessException {
        String index = (String) ConfigurationReader.getInstance().getValueByKey("dataBase","internalKey");
        Map<String,String> conditionsMap = new HashMap<>();
        Map<String,String> newFieldMap = new HashMap<>();
        StringBuilder sql= new StringBuilder();
        Class<?> cl = model.getClass();
        Field[] fields = cl.getDeclaredFields();
        AccessibleObject.setAccessible(fields,true);
        for(Field field:fields){
            if(field.get(model)!=null){
                if(field.getName().equals(index)){
                    conditionsMap.put(index,String.valueOf(field.get(model)));
                }else {
                    newFieldMap.put(Built.toColumnName(field.getName()),String.valueOf(field.get(model)));
                }
            }
        }
        //开始拼接SQL语句
        sql.append("UPDATE ").append(Built.toTableName(cl.getSimpleName())).append(" SET ");
        for (Map.Entry<String, String> entry : newFieldMap.entrySet()) {
            String mapKey = entry.getKey();
            String mapValue = entry.getValue();
            sql.append(mapKey).append("=").append("\"").append(mapValue).append("\"").append(",");
        }
        sql.replace(sql.lastIndexOf(","),sql.lastIndexOf(",")+1," ");
        sql.append("WHERE ");

        for (Map.Entry<String, String> entry : conditionsMap.entrySet()) {
            String mapKey = entry.getKey();
            String mapValue = entry.getValue();
            sql.append(mapKey).append("=").append("\"").append(mapValue).append("\"").append(" ").append("AND ");
        }
        sql.replace(sql.lastIndexOf("AND"),sql.lastIndexOf("AND")+3," ");

        return sql.toString();
    }

    /**
     * 自动生成update语句: update table_name set ..... where ....
     * 其中model中有值的字段将作为更新内容，conditionsMap中的键值对作为更新条件
     * 支持置空
     * @param model 表模型
     * @param conditionsMap 更新条件
     * @return 返回SQL语句
     */
    public String updateGeneratorAllowNull(T model, Map<String,String> conditionsMap) throws IllegalAccessException{
        Map<String,String> newFieldMap = new HashMap<>();
        StringBuilder sql= new StringBuilder();
        Class<?> cl = model.getClass();
        Field[] fields = cl.getDeclaredFields();
        AccessibleObject.setAccessible(fields,true);
        for(Field field:fields){
            newFieldMap.put(Built.toColumnName(field.getName()),String.valueOf(field.get(model)));
        }
        //开始拼接SQL语句
        sql.append("UPDATE ").append(Built.toTableName(cl.getSimpleName())).append(" SET ");
        for (Map.Entry<String, String> entry : newFieldMap.entrySet()) {
            String mapKey = entry.getKey();
            String mapValue = entry.getValue();
            sql.append(mapKey).append("=").append("\"").append(mapValue).append("\"").append(",");
        }
        sql.replace(sql.lastIndexOf(","),sql.lastIndexOf(",")+1," ");
        sql.append("WHERE ");
        for (Map.Entry<String, String> entry : conditionsMap.entrySet()) {
            String mapKey = entry.getKey();
            String mapValue = entry.getValue();
            sql.append(Built.toColumnName(mapKey)).append("=").append("\"").append(mapValue).append("\"").append(" ").append("AND ");
        }
        sql.replace(sql.lastIndexOf("AND"),sql.lastIndexOf("AND")+3," ");

        return sql.toString();
    }

    /**
     * 自动生成delete语句
     * 自动根据model中的字段值进行删除数据
     * @param model
     * @return
     */
    public String deleteGenerator(T model) throws IllegalAccessException {
        Map<String,String> conditionsMap = new HashMap<>();
        StringBuilder sql= new StringBuilder();
        Class<?> cl = model.getClass();
        Field[] fields = cl.getDeclaredFields();
        AccessibleObject.setAccessible(fields,true);
        for(Field field:fields){
            if(field.get(model)!=null){
                conditionsMap.put(Built.toColumnName(field.getName()),String.valueOf(field.get(model)));
            }
        }
        //开始拼接SQL
        sql.append("DELETE FROM ").append(Built.toTableName(cl.getSimpleName())).append(" WHERE ");
        for (Map.Entry<String, String> entry : conditionsMap.entrySet()) {
            String mapKey = entry.getKey();
            String mapValue = entry.getValue();
            sql.append(mapKey).append("=").append("\"").append(mapValue).append("\"").append(" ").append("AND ");
        }
        sql.replace(sql.lastIndexOf("AND"),sql.lastIndexOf("AND")+3," ");

        return sql.toString();
    }
    @Test
    public void deleteSqlTest() throws IllegalAccessException {
        User user =new User();
        user.setPassword("32354235");
        user.setUname("严林静");
        System.out.println(deleteGenerator((T) user));
    }
    /**
     * 自动生成insert语句
     * @param model
     * @return
     */
    public String insertGenerator(T model) throws IllegalAccessException {
        Map<String,String> newFieldMap = new HashMap<>();
        StringBuilder sql= new StringBuilder();
        StringBuilder valueKey = new StringBuilder();
        StringBuilder values = new StringBuilder();
        Class<?> cl = model.getClass();
        Field[] fields = cl.getDeclaredFields();
        AccessibleObject.setAccessible(fields,true);
        for(Field field:fields){
            if(field.get(model)!=null){
                newFieldMap.put(Built.toColumnName(field.getName()),String.valueOf(field.get(model)));
            }
        }
        //开始拼接SQL
        sql.append("INSERT INTO ").append(Built.toTableName(cl.getSimpleName())).append("(");
        for (Map.Entry<String, String> entry : newFieldMap.entrySet()) {
            String mapKey = entry.getKey();
            String mapValue = entry.getValue();
            valueKey.append(mapKey).append(",");
            values.append("\"").append(mapValue).append("\"").append(",");
        }
        valueKey.replace(valueKey.lastIndexOf(","),valueKey.lastIndexOf(",")+1,"");
        values.replace(values.lastIndexOf(","),values.lastIndexOf(",")+1,"");
        sql.append(valueKey).append(") ").append("VALUES(").append(values).append(")");
        return sql.toString();
    }
    @Test
    public void insertSqlTest() throws IllegalAccessException {
        User user =new User();
        user.setPassword("32354235");
        user.setCode("22222");
        System.out.println(insertGenerator((T) user));
    }

}
